Imports System
Public Enum ClearingStatuses
	NewRequest = 0
	Submitted = 1
	Cleared = 2
    GatheringData = 5
    SubmitResolved = 6
    AcctRefreshed = 7
    VoidRequest = 8
    PasswordChanged = 10
    DataRetreived = 11
    Voided = 12
	UnresolvedData = 90
	PasswordChangeFailed = 91
	SubmissionError = 92
	NoCPCID = 93
    LogonFailed = 94
    AcctRefreshFailed = 95
End Enum

Public Class ClearingQueue

	Protected dbi As dbInterface = Globals.dbi
    Protected m_lastmsg As String
    Protected m_loggedOn As Boolean
    Protected m_logonMsg As LogonMsg

	Public Property LastMsg() As String
		Get
			Return m_lastmsg
		End Get
		Set(ByVal Value As String)
			m_lastmsg = Value
		End Set
	End Property
	Public Sub New()
        m_loggedOn = False
        m_logonMsg = New LogonMsg
	End Sub

    Public Function LogoffCPCID() As Boolean
        If m_loggedOn = False Then
            Return False
        End If

        m_logonMsg.LogonType = UserReqTypes.Logoff
        m_logonMsg.Submit()

        '        m_logonMsg = Nothing
        m_loggedOn = False
        Return True
    End Function
    Public Function LogonCPCID() As Boolean
        If m_loggedOn Then
            Return True
        End If
        Dim dbi As New dbInterface
        Dim sql As String
        sql = "select nymx_login, nymx_pass from users where UserId=0"
        'sql &= " union select nymx_login, nymx_pass from users where UserId=0"

        dbi.query(sql)

        If dbi.eof Then
            Return False
        End If

        If dbi.getval("nymx_login") = "" Then
            dbi.movenext()
        End If

        Globals.UserName = dbi.getval("nymx_login")
        'm_logonMsg.UserName = dbi.getval("nymx_login")
        m_logonMsg.Password = dbi.getval("nymx_pass")

        m_logonMsg.LogonType = UserReqTypes.Logon
        m_logonMsg.Submit()

        If m_logonMsg.ErrMsg <> "" Then
            Return False
        End If

        m_loggedOn = True
        Return True
    End Function

    ' 20071009 - added ability to get accounts based on request that includes a product id
    Public Function CheckForNewItems() As Boolean
        'if data was found, return true, else return false
        Dim sql As String
        Dim refid As Long
        Dim prodid As String
		Dim loggedin As Boolean
		Dim clearid As Long
        ' get all rows where clearing is for nymex and they are new (status=0)\
        Try

            sql = "select * from clearing_trades where clearing_house=43 and clearing_status=0 order by dt_queued"

            dbi.query(sql)

            If dbi.eof = False Then
                loggedin = LogonCPCID()
                'Check type of clearing request. If refid=0 then it is really a password change request. otherwise, clearing request
                refid = dbi.getval("RefId")
				prodid = dbi.getval("product_id")
				clearid = dbi.getval("clear_id")

                LastMsg = refid

                If refid = 0 Then
                    If prodid <> "" Then
                        If loggedin Then
							RequestAcctUpdate(prodid, clearid)
                        End If
                    Else
						RequestPasswordChange(clearid)
                    End If
                Else
                    If loggedin Then
                        RequestTradeClearing()
                    Else
						RequestPasswordChange(clearid)
                    End If
                End If

                Return True
            End If
        Catch ex As Exception
            Log.LogData(ex.ToString & "\n" & ex.StackTrace)
            Return False
        End Try

        Return False
    End Function

    Public Function CheckForResolvedData() As Boolean
        'if data was found, return true, else return false
        Dim sql As String
        Dim refid As Long
        ' get all rows where clearing is for nymex and they are new (status=0)
        sql = "select * from clearing_trades where clearing_house=43 and clearing_status=6 order by dt_queued"

        dbi.query(sql)

        If dbi.eof = False Then
            If LogonCPCID() = False Then
                Return False
            End If

            refid = dbi.getval("RefId")

            LastMsg = refid

            Dim clearid As String = dbi.getval("clear_id")

            'if any data does not match up, will need to log each piece needing resolution into a table
            Dim tcstocpc As New TcsDataToCPC(clearid, refid)

            tcstocpc.CheckData()
            Return True
        End If

        Return False
    End Function

    Public Function CheckForWaitingItems() As Boolean
        'if data was found, return true, else return false
        Dim sql As String
        Dim refid As Long
        Dim status As Long
        ' get all rows where clearing is for nymex and they are new (status=0)
        sql = "select * from clearing_trades where clearing_house=43 and clearing_status in (1,8) and dateadd(s, 5, dt_lastupdate) < getdate() order by dt_queued"

        dbi.query(sql)

        If dbi.eof = False Then
            If LogonCPCID() = False Then
                Return False
            End If

            refid = dbi.getval("RefId")

            LastMsg = refid

            Dim clearid As String = dbi.getval("clear_id")

            'if any data does not match up, will need to log each piece needing resolution into a table
            Dim tcstocpc As New TcsDataToCPC(clearid, refid)

            status = dbi.getval("clearing_status")
            If status = ClearingStatuses.VoidRequest Then
                tcstocpc.VoidRequest()
            Else
                tcstocpc.CheckStatus()
            End If
            Return True
        End If

        Return False
    End Function
	Public Sub RunClearing()
		'RequestTradeClearing()

		Dim tcstocpc As New TcsDataToCPC("30582", "48830")

		tcstocpc.CheckData()

	End Sub

    Private Sub RequestTradeClearing()
        Dim clearid As String = dbi.getval("clear_id")
        Dim refid As String = dbi.getval("RefId")

        'need to first build the trade data to be submitted from the TCS data.
        'if any data does not match up, will need to log each piece needing resolution into a table
        Dim tcstocpc As New TcsDataToCPC(clearid, refid)

        tcstocpc.ConvertData()

        '		If tcstocpc.ErrMsg <> "" Then
        '		UpdateStatus(clearid, ClearingStatuses.SubmissionError, tcstocpc.ErrMsg)
        '		End If
    End Sub

    Public Sub UpdateAllAccts()
        Dim sql As String
        Dim ra() As String
        Dim tstr As String = ""
        Dim x As Long

        sql = "select distinct cpc_prod_id from clearing_tcs_cpc_xr x inner join nymx_products p on x.cpc_prod_id=p.prod_id"

        dbi.query(sql)

        While dbi.eof = False
            tstr &= dbi.getval("cpc_prod_id") & ","

            dbi.movenext()
        End While
        ra = tstr.Split(",")

        For x = 0 To ra.Length - 1
            If ra(x).Trim() <> "" Then
				RequestAcctUpdate(ra(x))	', 0, 0)
            End If
        Next
    End Sub

	Public Sub RequestAcctUpdate(ByVal prodid As String, Optional ByVal clear As Long = -1)
		Dim sql As String

		sql = "Select prod_id, sec_typ from nymx_products where prod_id=" & dbi.quote(prodid)

		dbi.query(sql)

		If dbi.eof Then
			Exit Sub
		End If


		Dim amsg As New AccountsMsg

		amsg.ProductID = prodid
		amsg.SecTyp = dbi.getval("sec_typ")	'Globals.TranslateType(dbi.getval("cfi"))
		amsg.Submit()

		If clear = -1 Then
			Exit Sub
		End If

		'Need to relink up account info
		dbi.SetupStoredProc("sp_nymx_acct_xr_name")
		dbi.RunStoredProc()

		UpdateStatus(clear, ClearingStatuses.AcctRefreshed)

		'lm.LogonType = UserReqTypes.Logoff
		'lm.Submit()

	End Sub

	Private Sub RequestPasswordChange(ByVal clearid As Long)
		Dim userid As Long
		'Dim clearid As Long
		Dim sql As String

		userid = 0 'dbi.getval("clearing_broker_id")
		'clearid = 0	'dbi.getval("clear_id")
		'		dtqueued = dbi.getval("dt_queued")

		sql = "select nymx_login, nymx_pass, nymx_newpass from users where UserId=" & userid

		dbi.query(sql)

		If dbi.eof Then
			Exit Sub
		End If
		Dim nlogin As String
		Dim npass As String
		Dim nnpass As String

		nlogin = dbi.getval("nymx_login")
		npass = dbi.getval("nymx_pass")
		nnpass = dbi.getval("nymx_newpass")

		LogoffCPCID()
		Dim lm As New LogonMsg

		'lm.UserName = nlogin
		lm.Password = npass

		lm.LogonType = UserReqTypes.Logon

		If lm.Submit() = False Then
			' logon failed - log the reason
			UpdateStatus(clearid, ClearingStatuses.PasswordChangeFailed, lm.ErrMsg)
		Else
			lm.NewPassword = nnpass
			lm.LogonType = UserReqTypes.ChangePassword
			If lm.Submit() = False Then
				'change request failed - log the reason
				UpdateStatus(clearid, ClearingStatuses.PasswordChangeFailed, lm.ErrMsg)
			Else
				' check to make sure no error message was sent back
				If lm.ErrMsg <> "" Then
					UpdateStatus(clearid, ClearingStatuses.PasswordChangeFailed, lm.ErrMsg)
				Else
					'password was changed
					UpdateStatus(clearid, ClearingStatuses.PasswordChanged)
					UpdatePasswordInfo(userid, nnpass)
				End If
			End If
		End If

		lm.LogonType = UserReqTypes.Logoff
		lm.Submit()

	End Sub

    Private Sub UpdatePasswordInfo(ByVal userid As Long, ByVal newpass As String)

        Dim sql As String

        sql = "update Users set nymx_pass=" & dbi.quote(newpass)
        sql &= ", nymx_newpass='', nymx_exp=getdate() "
        sql &= " where UserId=" & userid

        dbi.nonquery(sql)

        sql = "insert into nymx_password_hist (UserId, password) values(" & userid & "," & dbi.quote(newpass) & ")"

        dbi.nonquery(sql)
    End Sub

    Private Sub UpdateStatus(ByVal clearid As Long, ByVal newstat As ClearingStatuses)
        UpdateStatus(clearid, newstat, "")
    End Sub

    Private Sub UpdateStatus(ByVal clearid As Long, ByVal newstat As ClearingStatuses, ByVal msgtxt As String)
        Dim sql As String

        sql = "update clearing_trades set message_text=" & dbi.quote(msgtxt) & ", clearing_status=" & newstat.GetHashCode
        sql &= ", dt_lastupdate=getdate()"
        sql &= " where clear_id=" & clearid

        dbi.nonquery(sql)
    End Sub
End Class
